﻿using JNPF.Utils;
using System;
using System.Collections;
using System.Data;
using System.Data.Common;
using System.Text;

namespace JNPF.Data.Util
{
    /// <summary>
    /// 拼接SQL语句
    /// 版 本：V3.0.0
    /// 版 权：引迈信息技术有限公司（https://www.jnpfsoft.com）
    /// 作 者：JNPF开发平台组
    /// </summary>
    public static class DbSqlBuilder
    {
        /// <summary>
        /// 生成Insert语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="ht">Hashtable</param>
        /// <returns>返回sql</returns>
        public static StringBuilder InsertSql(string tableName, Hashtable ht)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" Insert Into ");
            sb.Append(tableName);
            sb.Append("(");
            StringBuilder sp = new StringBuilder();
            StringBuilder sb_prame = new StringBuilder();
            foreach (string key in ht.Keys)
            {
                if (ht[key] != null)
                {
                    sb_prame.Append("," + key);
                    sp.Append(",@" + key);
                }
            }
            sb.Append(sb_prame.ToString().Substring(1, sb_prame.ToString().Length - 1) + ") Values (");
            sb.Append(sp.ToString().Substring(1, sp.ToString().Length - 1) + ")");
            return sb;
        }
        /// <summary>
        /// 生成UpdateSql语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="ht">Hashtable</param>
        /// <param name="pkName">主键</param>
        /// <returns></returns>
        public static StringBuilder UpdateSql(string tableName, Hashtable ht, string pkName)
        {
            StringBuilder sb = new StringBuilder();
            sb.Append(" Update ");
            sb.Append(tableName);
            sb.Append(" Set ");
            bool isFirstValue = true;
            foreach (string key in ht.Keys)
            {
                if (ht[key] != null && pkName != key)
                {
                    if (isFirstValue)
                    {
                        isFirstValue = false;
                        sb.Append(key);
                        sb.Append("=");
                        sb.Append("@" + key);
                    }
                    else
                    {
                        sb.Append("," + key);
                        sb.Append("=");
                        sb.Append("@" + key);
                    }
                }
            }
            sb.Append(" Where ").Append(pkName).Append("=").Append("@" + pkName);
            return sb;
        }
        /// <summary>
        /// 生成Delete语句
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>返回sql</returns>
        public static StringBuilder DeleteSql(string tableName)
        {
            var sb = new StringBuilder();
            sb.Append($"DELETE FROM {tableName} ");
            return sb;
        }
        /// <summary>
        /// 转换分页SQL语句
        /// </summary>
        /// <param name="pageInput">分页参数</param>
        /// <param name="strSql">sql语句</param>
        /// <param name="dbType">数据库类型</param>
        public static Hashtable QueryPageSql(PageInput pageInput, string strSql, DatabaseType dbType)
        {
            string orderBy = "";
            if (!string.IsNullOrEmpty(pageInput.sidx))
                orderBy = " ORDER BY " + pageInput.sidx + " " + pageInput.sord;
            else
            {
                if (!dbType.Equals(DatabaseType.Oracle))
                {
                    orderBy = " ORDER BY (SELECT 0)";
                }
            }
            Hashtable multipleSql = new Hashtable();
            multipleSql["sqlCount"] = "SELECT COUNT(1) FROM (" + strSql + ") T";
            switch (dbType)
            {
                case DatabaseType.SqlServer:
                    multipleSql["sqlText"] = SqlPageSql(strSql, pageInput.pageSize, pageInput.currentPage, orderBy);
                    break;
                case DatabaseType.Oracle:
                    multipleSql["sqlText"] = OraclePageSql(strSql, pageInput.pageSize, pageInput.currentPage, orderBy);
                    break;
                case DatabaseType.MySql:
                    multipleSql["sqlText"] = MySqlPageSql(strSql, pageInput.pageSize, pageInput.currentPage, orderBy);
                    break;
            }
            return multipleSql;
        }
        /// <summary>
        /// sql分页语句
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="pageSize">每页数据条数</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="orderBy">排序语句</param>
        /// <returns></returns>
        public static StringBuilder SqlPageSql(string strSql, int pageSize, int pageIndex, string orderBy)
        {
            StringBuilder sb = new StringBuilder();
            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int num = (pageIndex - 1) * pageSize;
            int num1 = (pageIndex) * pageSize;
            sb.Append("SELECT * FROM (SELECT ROW_NUMBER() OVER (" + orderBy + ")");
            sb.Append(" AS ROWNUM, * FROM (" + strSql + ")  T ) AS N WHERE ROWNUM > " + num + " AND ROWNUM <= " + num1 + "");
            return sb;
        }
        /// <summary>
        /// oracle分页语句
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="pageSize">每页数据条数</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="orderBy">排序语句</param>
        /// <returns></returns>
        public static StringBuilder OraclePageSql(string strSql, int pageSize, int pageIndex, string orderBy)
        {
            StringBuilder sb = new StringBuilder();
            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int num = (pageIndex - 1) * pageSize;
            int num1 = (pageIndex) * pageSize;
            sb.Append("SELECT * FROM(SELECT T.*,ROWNUM RN FROM(" + strSql + orderBy + ") T WHERE ROWNUM <= " + num1 + ") WHERE RN > " + num + "");
            return sb;
        }
        /// <summary>
        /// mysql分页语句
        /// </summary>
        /// <param name="strSql">sql语句</param>
        /// <param name="pageSize">每页数据条数</param>
        /// <param name="pageIndex">页码</param>
        /// <param name="orderBy">排序语句</param>
        /// <returns></returns>
        public static StringBuilder MySqlPageSql(string strSql, int pageSize, int pageIndex, string orderBy)
        {
            StringBuilder sb = new StringBuilder();
            if (pageIndex == 0)
            {
                pageIndex = 1;
            }
            int num = (pageIndex - 1) * pageSize;
            sb.Append(strSql + orderBy);
            sb.Append(" LIMIT " + num + "," + pageSize + "");
            return sb;
        }
        /// <summary>
        /// 数据库表SQL
        /// </summary>
        /// <param name="dbType">数据库类型</param>
        /// <returns></returns>
        public static string DBTableSql(DatabaseType dbType)
        {
            StringBuilder sb = new StringBuilder();
            switch (dbType)
            {
                case DatabaseType.SqlServer:
                    sb.Append(@"DECLARE @TABLEINFO TABLE ( NAME VARCHAR(50) , SUMROWS VARCHAR(11) , RESERVED VARCHAR(50) , DATA VARCHAR(50) , INDEX_SIZE VARCHAR(50) , UNUSED VARCHAR(50) , PK VARCHAR(50) ) DECLARE @TABLENAME TABLE ( NAME VARCHAR(50) ) DECLARE @NAME VARCHAR(50) DECLARE @PK VARCHAR(50) INSERT INTO @TABLENAME ( NAME ) SELECT O.NAME FROM SYSOBJECTS O , SYSINDEXES I WHERE O.ID = I.ID AND O.XTYPE = 'U' AND I.INDID < 2 ORDER BY I.ROWS DESC , O.NAME WHILE EXISTS ( SELECT 1 FROM @TABLENAME ) BEGIN SELECT TOP 1 @NAME = NAME FROM @TABLENAME DELETE @TABLENAME WHERE NAME = @NAME DECLARE @OBJECTID INT SET @OBJECTID = OBJECT_ID(@NAME) SELECT @PK = COL_NAME(@OBJECTID, COLID) FROM SYSOBJECTS AS O INNER JOIN SYSINDEXES AS I ON I.NAME = O.NAME INNER JOIN SYSINDEXKEYS AS K ON K.INDID = I.INDID WHERE O.XTYPE = 'PK' AND PARENT_OBJ = @OBJECTID AND K.ID = @OBJECTID INSERT INTO @TABLEINFO ( NAME , SUMROWS , RESERVED , DATA , INDEX_SIZE , UNUSED ) EXEC SYS.SP_SPACEUSED @NAME UPDATE @TABLEINFO SET PK = @PK WHERE NAME = @NAME END SELECT F.NAME AS F_TABLE,ISNULL(P.TDESCRIPTION,F.NAME) AS F_TABLENAME, F.RESERVED AS F_SIZE, RTRIM(F.SUMROWS) AS F_SUM, F.PK AS F_PRIMARYKEY FROM @TABLEINFO F LEFT JOIN ( SELECT NAME = CASE WHEN A.COLORDER = 1 THEN D.NAME ELSE '' END , TDESCRIPTION = CASE WHEN A.COLORDER = 1 THEN ISNULL(F.VALUE, '') ELSE '' END FROM SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE = B.XUSERTYPE INNER JOIN SYSOBJECTS D ON A.ID = D.ID AND D.XTYPE = 'U' AND D.NAME <> 'DTPROPERTIES' LEFT JOIN SYS.EXTENDED_PROPERTIES F ON D.ID = F.MAJOR_ID WHERE A.COLORDER = 1 AND F.MINOR_ID = 0 ) P ON F.NAME = P.NAME WHERE 1 = 1 ORDER BY F_TABLE");
                    break;
                case DatabaseType.Oracle:
                    sb.Append(@"SELECT DISTINCT COL.TABLE_NAME AS F_TABLE,TAB.COMMENTS AS F_TABLENAME,0 AS F_SIZE,NVL(T.NUM_ROWS,0)AS F_SUM,COLUMN_NAME AS F_PRIMARYKEY FROM USER_CONS_COLUMNS COL INNER JOIN USER_CONSTRAINTS CON ON CON.CONSTRAINT_NAME=COL.CONSTRAINT_NAME INNER JOIN USER_TAB_COMMENTS TAB ON TAB.TABLE_NAME=COL.TABLE_NAME INNER JOIN USER_TABLES T ON T.TABLE_NAME=COL.TABLE_NAME WHERE CON.CONSTRAINT_TYPE NOT IN('C','R')ORDER BY COL.TABLE_NAME");
                    break;
                case DatabaseType.MySql:
                    sb.Append(@"SELECT T1.*,(SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.`COLUMNS`WHERE TABLE_SCHEMA=DATABASE()AND TABLE_NAME=T1.F_TABLE AND COLUMN_KEY='PRI')F_PRIMARYKEY FROM(SELECT TABLE_NAME F_TABLE,0 F_SIZE,TABLE_ROWS F_SUM,(SELECT IF(LENGTH(TRIM(TABLE_COMMENT))<1,TABLE_NAME,TABLE_COMMENT))F_TABLENAME FROM INFORMATION_SCHEMA.`TABLES`WHERE TABLE_SCHEMA=DATABASE())T1 ORDER BY T1.F_TABLE");
                    break;
                default:
                    throw new Exception("不支持");
            }
            return sb.ToString();
        }
        /// <summary>
        /// 数据库表字段SQL
        /// </summary>
        /// <param name="dBType">数据库类型</param>
        /// <returns></returns>
        public static string DBTableField(DatabaseType dbType)
        {
            StringBuilder sb = new StringBuilder();
            switch (dbType)
            {
                case DatabaseType.SqlServer:
                    sb.Append(@"SELECT [F_FIELD] =A.NAME , [F_DATATYPE]=B.NAME, [F_DATALENGTH]=COLUMNPROPERTY(A.ID,A.NAME,'PRECISION'), [F_IDENTITY]=CASE WHEN COLUMNPROPERTY( A.ID,A.NAME,'ISIDENTITY')=1 THEN '√'ELSE '' END, [F_PRIMARYKEY]=CASE WHEN EXISTS(SELECT 1 FROM SYSOBJECTS WHERE XTYPE='PK' AND PARENT_OBJ=A.ID AND NAME IN ( SELECT NAME FROM SYSINDEXES WHERE INDID IN( SELECT INDID FROM SYSINDEXKEYS WHERE ID = A.ID AND COLID=A.COLID ))) THEN '1' ELSE '0' END, [F_ALLOWNULL]=CASE WHEN A.ISNULLABLE=1 THEN '1'ELSE '0' END, [F_DEFAULTS]=ISNULL(E.TEXT,''), [F_FIELDNAME]=ISNULL(G.[VALUE],A.NAME) FROM SYSCOLUMNS A LEFT JOIN SYSTYPES B ON A.XUSERTYPE=B.XUSERTYPE INNER JOIN SYSOBJECTS D ON A.ID=D.ID AND D.XTYPE='U' AND D.NAME<>'DTPROPERTIES' LEFT JOIN SYSCOMMENTS E ON A.CDEFAULT=E.ID LEFT JOIN SYS.EXTENDED_PROPERTIES G ON A.ID=G.MAJOR_ID AND A.COLID=G.MINOR_ID LEFT JOIN SYS.EXTENDED_PROPERTIES F ON D.ID=F.MAJOR_ID AND F.MINOR_ID=0 WHERE D.NAME='{0}' ORDER BY A.ID,A.COLORDER");
                    break;
                case DatabaseType.Oracle:
                    sb.Append(@"SELECT COL.COLUMN_NAME F_FIELD, COL.DATA_TYPE F_DATATYPE, COL.DATA_LENGTH F_DATALENGTH, NULL F_IDENTITY, CASE UC.CONSTRAINT_TYPE WHEN 'P' THEN 1 ELSE NULL END F_PRIMARYKEY, CASE COL.NULLABLE WHEN 'N' THEN 0 ELSE 1 END F_ALLOWNULL, COL.DATA_DEFAULT F_DEFAULTS, COMM.COMMENTS AS F_FIELDNAME FROM USER_TAB_COLUMNS COL INNER JOIN USER_COL_COMMENTS COMM ON COMM.TABLE_NAME = COL.TABLE_NAME AND COMM.COLUMN_NAME = COL.COLUMN_NAME LEFT JOIN USER_CONS_COLUMNS UCC ON UCC.TABLE_NAME = COL.TABLE_NAME AND UCC.COLUMN_NAME = COL.COLUMN_NAME AND UCC.POSITION=1 LEFT JOIN USER_CONSTRAINTS UC ON UC.CONSTRAINT_NAME = UCC.CONSTRAINT_NAME AND UC.CONSTRAINT_TYPE = 'P' WHERE COL.TABLE_NAME = '{0}' ORDER BY COL.COLUMN_ID");
                    break;
                case DatabaseType.MySql:
                    sb.Append(@"SELECT ORDINAL_POSITION F_NUMBER,COLUMN_NAME F_FIELD,DATA_TYPE F_DATATYPE,IF(CHARACTER_MAXIMUM_LENGTH IS NULL,IF(LOCATE('INT',COLUMN_TYPE)>0,REPLACE(REPLACE(COLUMN_TYPE,'INT(',''),')',''),0),CHARACTER_MAXIMUM_LENGTH)F_DATALENGTH,''F_IDENTITY,IF(COLUMN_KEY='PRI','1','')F_PRIMARYKEY,IF(IS_NULLABLE='YES','1','')F_ALLOWNULL,COLUMN_DEFAULT F_DEFAULTS,CASE WHEN COLUMN_COMMENT=''THEN COLUMN_NAME ELSE COLUMN_COMMENT END F_FIELDNAME FROM(SELECT*FROM INFORMATION_SCHEMA.`COLUMNS`T1 WHERE TABLE_SCHEMA=DATABASE()AND TABLE_NAME='{0}')T2 ORDER BY F_NUMBER");
                    break;
                default:
                    throw new Exception("不支持");
            }
            return sb.ToString();
        }
        /// <summary>
        /// 转换DbParameter
        /// </summary>
        /// <param name="ht"></param>
        /// <returns></returns>
        public static DbParameter[] ToDbParameter(Hashtable ht, DatabaseType databaseType)
        {
            try
            {
                DbParameters dbParameter = new DbParameters();
                DbType dbtype = new DbType();
                foreach (string key in ht.Keys)
                {
                    if (ht[key] != null)
                    {
                        if (ht[key] is DateTime)
                            dbtype = DbType.DateTime;
                        else
                            dbtype = DbType.String;
                        dbParameter.Add("@" + key, ht[key], databaseType, dbtype);
                    }
                }
                return dbParameter.parameterList.ToArray();

            }
            catch (Exception ex)
            {
                LogFactory.GetLogger().Error(ex);
                throw;
            }
        }
    }
}
